set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;


with warehousing_sum as (
    select 
        scan_site_code
        ,sum(weight_cnt)                   as weight_cnt
        ,sum(manual_weight_cnt)            as manual_weight_cnt
        ,sum(month_acc_weight_cnt)         as month_acc_weight_cnt
        ,sum(month_acc_manual_weight_cnt)  as month_acc_manual_weight_cnt
        ,sum(month_acc_over_rate_cnt)      as month_acc_over_rate_cnt
    from(
        -- 当日数据
        select 
            scan_site_code
            ,count(waybill_no)                                              as weight_cnt                  -- 称重数
            ,count(case when weight_type = 1 then waybill_no end )          as manual_weight_cnt           -- 手工称重数
            ,count(waybill_no)                                              as month_acc_weight_cnt        -- 月累计称重数
            ,count(case when weight_type = 1 then waybill_no end )          as month_acc_manual_weight_cnt -- 月累计手工称重数
            ,case when count(waybill_no) > 0 
                            and count(case when weight_type = 1 then waybill_no end ) / count(waybill_no) > 0.5 
                            then 1 
                  else 0 
            end             as month_acc_over_rate_cnt
        from (
            select 
                waybill_no
                ,scan_site_code
                ,input_time
                ,weight_type
                ,row_number() over(partition by waybill_no, scan_site_code order by input_time desc) as rn
            from 
                jms_dwd.dwd_tab_barscan_warehousing_base_dt
            where 
                dt = '{{ execution_date | cst_ds }}'
                and network_type = '4'
        ) t 
        where 
            rn = 1
        group by 
            scan_site_code

        union all 

        -- 前一天分区数据，但不能取到上个月
        select
            network_code
            ,0                        as weight_cnt             -- 称重数
            ,0                        as manual_weight_cnt      -- 手工称重数
            ,month_acc_weight_cnt                               -- 月累计称重数
            ,month_acc_manual_weight_cnt                        -- 月累计手工称重数
            ,month_acc_over_rate_cnt                            -- 月累计手工称重超50%网点数
        from 
            jms_dm.dm_warehousing_weight_type_sum_dt
        where 
            dt = date_sub('{{ execution_date | cst_ds }}', 1) 
            and dt >= substr('{{ execution_date | cst_ds }}', 1, 7) || '-01'
    ) t 
    group by 
        scan_site_code

)
insert overwrite table jms_dm.dm_warehousing_weight_type_sum_dt partition (dt = '{{ execution_date | cst_ds }}')
select 
    a.scan_site_code                        as network_code
    ,dim_network.name                       as network_name
    ,dim_network.agent_code                 as agent_code
    ,dim_network.agent_name                 as agent_name
    ,dim_network.city_id                    as city_id
    ,dim_network.city_desc                  as city_name
    ,dim_network.provider_id                as province_id
    ,dim_network.provider_desc              as province_name
    ,nvl(dim_network.zone_id, '0')          as zone_id
    ,nvl(dim_network.zone_code, '0')        as zone_code
    ,nvl(dim_network.zone_name, '/')        as zone_name
    ,nvl(dim_network.zone_staff_code, '0')  as zone_staff_code
    ,nvl(regexp_replace(dim_network.zone_staff_name, '[0-9/]', ''), '/') as area_staff_name
    ,nvl(dim_network.area_code, '0')        as area_code
    ,nvl(dim_network.area_name, '/')        as area_name
    ,nvl(dim_network.area_staff_code, '0')  as area_staff_code
    ,nvl(regexp_replace(dim_network.area_staff_name, '[0-9/]', ''), '/') as area_staff_name
    ,a.weight_cnt                           as weight_cnt
    ,a.manual_weight_cnt                    as manual_weight_cnt
    ,a.month_acc_weight_cnt                 as month_acc_weight_cnt         -- 月累计
    ,a.month_acc_manual_weight_cnt          as month_acc_manual_weight_cnt  -- 月累计   
    ,a.month_acc_over_rate_cnt              as month_acc_over_rate_cnt
from 
    warehousing_sum a
left join 
    jms_dim.dim_network_whole_massage dim_network
        on a.scan_site_code = dim_network.code
distribute by 1
;